/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.fa.supporters;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author tatung
 */
public class DbConnector {
    private static String dbName = "acc_db";
    public static Connection mysqlConn;
    private static String JDBC = "jdbc:mysql://localhost:3306/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true";

    private static int idPartnerTmp;
    private static String PartnerNameTmp;
    private static String DescriptionTmp;
    private static SimpleDateFormat sdf;
    
    public DbConnector() {
        mysqlConn = dbConnect(JDBC, "root", "");
    }
    
    public static Connection dbConnect(String db_connect_string, String db_userid, String db_password)
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password);

            System.out.println("connected");
            return conn;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return null;
        }
    }

    public static DefaultTableModel getPartnersTableModel(){
        try{
            //TableColumnModel columnModel = new DefaultTableColumnModel();
            DefaultTableModel model = new DefaultTableModel();
            model.addColumn("Mã số");
            model.addColumn("Tên");
            model.addColumn("Chi tiết");

            Statement s = DbConnector.mysqlConn.createStatement();
            s.executeQuery("SELECT * FROM " + dbName + ".Partner");
            ResultSet rs = s.getResultSet();
            while(rs.next()){
                idPartnerTmp = rs.getInt(1);
                PartnerNameTmp = rs.getString(2);
                DescriptionTmp = rs.getString(3);
                Object obj2[] = {idPartnerTmp, PartnerNameTmp, DescriptionTmp};
                model.addRow(obj2);
            }
            return model;
        } catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public static DefaultTableModel getBuyTransactionsTableModel(int idPartner,
                                                                 String productNameFilter,
                                                                 String strDateTimeFilterFrom,
                                                                 String strDateTimeFilterTo){
        DefaultTableModel model = new DefaultTableModel();
        model.addColumn("MS");
        model.addColumn("Ngày");
        model.addColumn("Giờ");
        model.addColumn("Hàng");
        model.addColumn("S.lg");
        model.addColumn("Đ.V");
        model.addColumn("Giá");
        model.addColumn("Tiền");
        model.addColumn("Ghi chú");
        try{
            Statement s = mysqlConn.createStatement();
            String q1 = "(SELECT idBuyTransaction, Date, BuyTransaction.idProduct, InProductName, Quantity, MetricName, Note "
                     + "FROM " + dbName + ".BuyTransaction, " + dbName + ".Product, " + dbName + ".Metric "
                     + "WHERE idPartner = " + idPartner
                     + " AND "
                     + "BuyTransaction.idProduct = Product.idProduct"
                     + " AND "
                     + "BuyTransaction.idMetric = Metric.idMetric) AS A";

            String q2 = "(SELECT * "
                      + "FROM acc_db.InPrice "
                      + "WHERE idPartner = " + idPartner + " "
                      + "ORDER BY Date DESC) AS B";
            
            String strProductNameFilterQuery;
            String strDateTimeFilterFromQuery;
            String strDateTimeFilterToQuery;
            
            if(productNameFilter.equals("Tất cả")){
                strProductNameFilterQuery = "";                
            }else{
                strProductNameFilterQuery = " AND A.InProductName = '" + productNameFilter + "' ";
            }

            if(strDateTimeFilterFrom.equals("") || strDateTimeFilterFrom == null){
                strDateTimeFilterFromQuery = "";
            }else{
                strDateTimeFilterFromQuery = " AND A.Date >= '" + strDateTimeFilterFrom + "' ";
            }

            if(strDateTimeFilterTo.equals("") || strDateTimeFilterTo == null){
                strDateTimeFilterToQuery = "";
            }else{
                strDateTimeFilterToQuery = " AND A.Date <= '" + strDateTimeFilterTo + "' ";
            }

            String q = "SELECT A.idBuyTransaction, A.Date, A.InProductName, A.Quantity, A.MetricName, B.InPrice, A.Quantity * B.InPrice AS Money, A.Note "
                        + " FROM " + q1 + ", " + q2 + " "
                        + " WHERE A.idProduct = B.idProduct "
                              + " AND "
                              + " A.Date - B.Date >= 0 "
                              + strProductNameFilterQuery + " "
                              + strDateTimeFilterFromQuery + " "
                              + strDateTimeFilterToQuery + " "
                        + " GROUP BY idBuyTransaction "
                        + " ORDER BY A.Date DESC, idBuyTransaction DESC";
//            String q = "SELECT idBuyTransaction, Date, InProductName, Quantity, MetricName, InPrice, Money, Note"
//                    + " FROM " + "(" + q3 + ") AS C "
//                    + " ORDER BY idBuyTransaction";
            
            System.out.println("###########: " + q);
            
//            String q = "SELECT A.idBuyTransaction, A.Date, A.InProductName, A.Quantity, A.MetricName, B.InPrice, A.Quantity * B.InPrice AS Money, A.Note "
//                      + "FROM    (SELECT idBuyTransaction, acc_db.BuyTransaction.Date, InProductName, Quantity, MetricName, Note "
//                              + "FROM  acc_db.BuyTransaction, acc_db.Product, acc_db.Metric "
//                              +    "WHERE idPartner = 1 "
//                              +    "AND "
//                              +    "BuyTransaction.idProduct = Product.idProduct "
//                              +    "AND "
//                              +    "BuyTransaction.idMetric = Metric.idMetric) AS A, "
//                              +    "(SELECT InPrice "
//                              +    "FROM acc_db.InPrice "
//                              +    "WHERE Date <= '2011-03-14 12:25:17' AND idProduct = 6 ORDER BY Date DESC LIMIT 1) AS B ";
            //System.out.println("!!!!!!!!!!!!!!: " + q1);
            
            ResultSet rs = s.executeQuery(q);

            while(rs.next()){
                int idBuyTransaction = rs.getInt("idBuyTransaction");
                String strDate = (new SimpleDateFormat("dd-MM-yy")).format(rs.getDate("Date")) + "";
                String strTime = (new SimpleDateFormat("HH:mm")).format(rs.getTime("Date")) + "";
                String productname = rs.getString("InProductName");
                float quantity = rs.getFloat("Quantity");
                String metric = rs.getString("MetricName");
                float inPrice = rs.getFloat("InPrice");
                float money = rs.getFloat("Money");
                String note = rs.getString("Note");
                Object[] obj = {idBuyTransaction, strDate, strTime, productname, quantity, metric, inPrice, money, note};
                model.addRow(obj);
                for(int i=0;i<obj.length;i++){
                    System.out.print(" " + obj[i]);
                }
                System.out.println();
            }
            return model;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public static boolean addPartner(String partnerName, String desc){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "INSERT INTO " + dbName + ".Partner (PartnerName, Description) VALUES('" + partnerName + "', '" + desc + "')";
            s.execute(q);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean addProduct(String inName, String outName){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "INSERT INTO " + dbName + ".Product (InProductName, OutProductName) VALUES('" + inName + "', '" + outName + "')";
            s.execute(q);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean deletePartner(int idPartner, String pName){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "DELETE FROM " + dbName + ".Partner WHERE idPartner = " + idPartner + " AND PartnerName = '" + pName + "'";
            s.execute(q);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public static Vector<String> getPartnerList(){
        Vector<String> list = new Vector<String>();
        try{
                Statement s = mysqlConn.createStatement();
                String q = "SELECT PartnerName FROM " + dbName + ".Partner";
                ResultSet rs = s.executeQuery(q);
                while(rs.next()){
                    String tmp = rs.getString("PartnerName");
                    System.out.println(tmp);
                    list.add(tmp);
                }
                return list;
            }catch (Exception e){
                e.printStackTrace();
                return list;
            }
    }

    public static Vector<String> getProductList(boolean isOutProductName){
        Vector<String> list = new Vector<String>();
        if(isOutProductName){
            try{
                Statement s = mysqlConn.createStatement();
                return list;
            }catch(Exception e){
                e.printStackTrace();
                return list;
            }
        } else{
            try{
                Statement s = mysqlConn.createStatement();
                String q = "SELECT InProductName FROM " + dbName + ".Product ORDER BY InProductName";
                ResultSet rs = s.executeQuery(q);
                while(rs.next()){
                    String tmp = rs.getString("InProductName");
                    System.out.println(tmp);
                    list.add(tmp);
                }
                return list;
            }catch (Exception e){
                e.printStackTrace();
                return list;
            }
        }
    }

    public static Vector<String> getMetricList(){
        Vector<String> ret = new Vector<String>();
        try{
            Statement s = mysqlConn.createStatement();
            String q = "SELECT MetricName FROM " + dbName + ".Metric";
            ResultSet rs = s.executeQuery(q);
            while(rs.next()){
                ret.add(rs.getString("MetricName"));
            }
            return ret;
        } catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public static boolean addInPrice(String strDate, String InProductName, String PartnerName, float inPrice){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "INSERT INTO " + dbName + ".InPrice(Date, idProduct, InPrice, idPartner) VALUES(" +
                    "'" + strDate + "', " +
                    "(select idProduct from " + dbName + ".Product where InProductName = '" + InProductName + "')," +
                    inPrice + ", " +
                    "(select idPartner from " + dbName + ".Partner where PartnerName = '" + PartnerName + "')" +
                    ")";
            s.execute(q);
            return true;
        } catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean addOutPrice(String strDate, String OutProductName, String PartnerName, float inPrice){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "INSERT INTO " + dbName + ".OutPrice(Date, idProduct, OutPrice, idPartner) VALUES(" +
                    "'" + strDate + "', " +
                    "(select idProduct from " + dbName + ".Product where OutProductName = '" + OutProductName + "')," +
                    inPrice + ", " +
                    "(select idPartner from " + dbName + ".Partner where PartnerName = '" + PartnerName + "')" +
                    ")";
            s.execute(q);
            return true;
        } catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }

    public static boolean addTransaction(boolean isOutTransaction,
                                         String strDate,
                                         String PartnerName,
                                         String ProductName,
                                         float quantity,
                                         String metric,
                                         String note){
        if(isOutTransaction){

        }else{
            try{
            Statement s = mysqlConn.createStatement();
            String q = "INSERT INTO " + dbName + ".BuyTransaction(Date, idPartner, idProduct, Quantity, idMetric, Note) VALUES(" +
                    "'" + strDate + "', " +
                    "(select idPartner from " + dbName + ".Partner where PartnerName = '" + PartnerName + "')," +
                    "(select idProduct from " + dbName + ".Product where InProductName = '" + ProductName + "')," +
                    quantity + ", " +
                    "(select idMetric from " + dbName + ".Metric where MetricName = '" + metric + "')," +
                    "'" + note + "'" + 
                    ")";
            s.execute(q);
            return true;
        } catch(Exception e){
            e.printStackTrace();
            return false;
        }
        }
        return true;
    }
//DefaultTableModel model = new DefaultTableModel();
//                model.addColumn("MS");
//                model.addColumn("Ngày");
//                model.addColumn("Giờ");
//                model.addColumn("Hàng");
//                model.addColumn("S.lg");
//                model.addColumn("Đ.V");
//                model.addColumn("Giá");
//                model.addColumn("Tiền");
//                model.addColumn("Ghi chú");
    public static boolean updateBuyTransaction(int idBuyTransaction,
                                               String strDateTime,
                                               String partnerName,
                                               String productName,
                                               float quantity,
                                               String metricName,
                                               String note){
        try{
            Statement s = DbConnector.mysqlConn.createStatement();
            String q;
            ResultSet rs;
            // get idPartner by partnerName
            q = "SELECT idPartner FROM Partner WHERE PartnerName = '" + partnerName +"'";
            rs = s.executeQuery(q);
            rs.next();
            int idPartner = Integer.parseInt(rs.getString("idPartner"));

            // get idProduct by productName
            q = "SELECT idProduct FROM Product WHERE InProductName = '" + productName +"'";
            rs = s.executeQuery(q);
            rs.next();
            int idProduct = Integer.parseInt(rs.getString("idProduct"));

            // get idMetric by metricName
            q = "SELECT idMetric FROM Metric WHERE MetricName = '" + metricName +"'";
            rs = s.executeQuery(q);
            rs.next();
            int idMetric = Integer.parseInt(rs.getString("idMetric"));

            // update row
            q = "UPDATE BuyTransaction SET "
                    + "Date = '" + strDateTime + "', "
                    + "idPartner = " + idPartner + ", "
                    + "idProduct = " + idProduct + ", "
                    + "Quantity = " + quantity + ", "
                    + "idMetric = " + idMetric + ", "
                    + "Note = " + note;
            s.execute(q);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }                        
    }

    public static boolean deleteBuyTransaction(int idBuyTransaction ){
        try{
            Statement s = mysqlConn.createStatement();
            String q = "DELETE FROM " + dbName + ".BuyTransaction WHERE idBuyTransaction = " + idBuyTransaction;
            s.execute(q);
            return true;
        }catch(Exception e){
            e.printStackTrace();
            return false;
        }
    }
}
